﻿using System;
using System.IO;

using Community.CsharpSqlite.SQLiteClient;

using log4net;

using VSTalk.Logger;

namespace VSTalk.Engine.Core.Data
{
    public class ConnectionFactory : IConnectionFactory
    {
        private static readonly ILog _log = LoggerFactory.Create(typeof(ConnectionFactory));
        
        private static readonly string _settingFolder = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);

        private static readonly string _dbFolder = Path.Combine(_settingFolder, DB_FILE_PATH);
        private static readonly string _dbFile = Path.Combine(_dbFolder, DB_FILE_NAME);

        private const string DB_FILE_PATH = @"VSTalk";
        private const string DB_FILE_NAME = "vstalk.sqlite";

        private MigrationService _migrationService;

        public ConnectionFactory(MigrationService migrationService)
        {
            _migrationService = migrationService;
        }

        public SqliteConnection GetConnection()
        {
            var connection = ConnectionString();
            var version = QueryVersion(connection);
            _migrationService.Update(connection, version);
            return connection;
        }

        private int QueryVersion(SqliteConnection connection)
        {
            connection.Open();
            try
            {
                var tableExists = connection.CreateCommand();
                tableExists.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='version';";
                var versionNumber = connection.CreateCommand();
                versionNumber.CommandText = "SELECT num FROM version;";
                _log.Info("Query version table");
                using (var reader = tableExists.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        _log.Info("Database is empty");
                        return 0;
                    }
                }
                _log.Info("Query version number");
                using (var reader = versionNumber.ExecuteReader())
                {
                    reader.Read();
                    var numb = (int) reader.GetValue(0);
                    _log.Info(string.Format("Database version is {0}", numb));
                    return numb;
                }
            }
            catch (Exception e)
            {
                _log.Fatal("Unexpected exception", e);
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        private SqliteConnection ConnectionString()
        {
            var connectionStr = string.Format("Data Source={0};Version=3;", _dbFile);
            var connection = new SqliteConnection(connectionStr);
            return connection;
        }

        private bool DatabaseExists()
        {
            return File.Exists(_dbFile);
        }
    }
}